package cn.demomaster.quickdb.utils;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

import cn.demomaster.quickdb.model.ColumnInfo;

public class DatabaseUtils {

    private static final String TAG = "DatabaseUtils";

    /**
     * 检查给定表中是否存在指定的字段。
     *
     * @param db          SQLiteDatabase 实例
     * @param tableName   表名
     * @param columnName  字段名
     * @return 如果字段存在则返回 true，否则返回 false
     */
    public static boolean isColumnExist(SQLiteDatabase db, String tableName, String columnName) {
        // 使用 PRAGMA table_info 获取表的列信息
        String queryStr = "PRAGMA table_info(" + escapeSqlIdentifier(tableName) + ");";
        Cursor cursor = db.rawQuery(queryStr, null);
        try {
            while (cursor.moveToNext()) {
                String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                if (name.equals(columnName)) {
                    Log.d(TAG, "字段: " + columnName + "，存在于表：" + tableName);
                    return true;
                }
            }
            Log.e(TAG, "字段未找到: " + columnName + " in table " + tableName);
            return false;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }
    public static List<ColumnInfo> getExistingColumns(SQLiteDatabase db, String tableName) {
        List<ColumnInfo> existingColumns = new ArrayList<>();
        Cursor cursor = db.rawQuery("PRAGMA table_info(" + escapeSqlIdentifier(tableName) + ");", null);
        if (cursor != null) {
            try {
                while (cursor.moveToNext()) {
                    ColumnInfo columnInfo= new ColumnInfo();
                    columnInfo.setColumnName(cursor.getString(cursor.getColumnIndex("name")));
                    columnInfo.setColumnType(cursor.getString(cursor.getColumnIndex("type")));
                    columnInfo.setNotNull(cursor.getInt(cursor.getColumnIndex("notnull"))==1);
                    columnInfo.setPrimaryKey(cursor.getInt(cursor.getColumnIndex("pk"))==1);
                    String defaultValue = cursor.getString(cursor.getColumnIndex("dflt_value"));
//                    // 转义默认值中的单引号
//                    if (defaultValue != null) {
//                        defaultValue = defaultValue.replace("'", "''");
//                    }
                    columnInfo.setDefaultValue(defaultValue);
                    existingColumns.add(columnInfo);
                }
            } finally {
                cursor.close();
            }
        }
        return existingColumns;
    }
    public static ColumnInfo getColumnByNameAndType(SQLiteDatabase db, String tableName, String columnName, String columnType) {
        // 使用 PRAGMA table_info 获取表的列信息
        String queryStr = "PRAGMA table_info(" + escapeSqlIdentifier(tableName) + ");";
        Cursor cursor = db.rawQuery(queryStr, null);
        try {
            while (cursor.moveToNext()) {
                String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                String type = cursor.getString(cursor.getColumnIndexOrThrow("type")).toUpperCase().trim();

                int columnCursorIndex3 = cursor.getColumnIndex("notnull");
                int notnull = cursor.getInt(columnCursorIndex3);

                int columnCursorIndex4 = cursor.getColumnIndex("pk");
                int pk = cursor.getInt(columnCursorIndex4);

                if (name.equals(columnName)) {
                    Log.d(TAG, "name=" + name + ", type=" + type+", notnull="+notnull+", pk="+pk);
                    // 检查类型匹配
                    if (type.equals(columnType)) {
                        ColumnInfo columnInfo= new ColumnInfo();
                        columnInfo.setNotNull(notnull==1);
                        columnInfo.setPrimaryKey(pk==1);
                        columnInfo.setColumnType(type);
                        columnInfo.setColumnName(name);
                        return columnInfo;
                    }
                }
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return null;
    }
    public static boolean checkColumnAndTypeMatch(SQLiteDatabase db, String tableName, String columnName, String columnType,boolean isNotnull,boolean ispk) {
        // 使用 PRAGMA table_info 获取表的列信息
        String queryStr = "PRAGMA table_info(" + escapeSqlIdentifier(tableName) + ");";
        Cursor cursor = db.rawQuery(queryStr, null);
        try {
            while (cursor.moveToNext()) {
                String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                String type = cursor.getString(cursor.getColumnIndexOrThrow("type")).toUpperCase().trim();

                int columnCursorIndex3 = cursor.getColumnIndex("notnull");
                int notnull = cursor.getInt(columnCursorIndex3);

                int columnCursorIndex4 = cursor.getColumnIndex("pk");
                int pk = cursor.getInt(columnCursorIndex4);

                if (name.equals(columnName)) {
                    Log.d(TAG, "name=" + name + ", type=" + type);
                    // 检查类型匹配
                    if (type.equals(columnType)) {
                        if (isNotnull==(notnull==1)){
                            if (ispk==(pk==1)){
                                return true;
                            }
                        }
                    } else {
                        Log.w(TAG, "字段类型不匹配: " + columnName + " 预期类型：" + columnType + " 实际类型：" + type);
                        return false;
                    }
                }
            }
            Log.w(TAG, "未找到字段: " + columnName);
            return false;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }
    /**
     * 辅助方法：转义 SQL 标识符以防止 SQL 注入
     */
    private static String escapeSqlIdentifier(String identifier) {
        return identifier.replace("\"", "\"\""); // 双引号转义为两个双引号
    }

    /**
     * 辅助方法：检查 SQLite 数据类型与 Java 类型是否匹配
     */
    private static boolean isTypeMatch(String sqliteType, Class<?> typeClass) {
        switch (sqliteType) {
            case "INTEGER":
                return typeClass == int.class || typeClass == Integer.class || typeClass == long.class || typeClass == Long.class || typeClass == boolean.class || typeClass == Boolean.class;
            case "REAL":
                return typeClass == float.class || typeClass == Float.class || typeClass == double.class || typeClass == Double.class;
            case "TEXT":
            case "VARCHAR":
            case "CHARACTER":
            case "CLOB":
                return typeClass == String.class;
            case "BLOB":
                return typeClass == byte[].class || typeClass == Byte[].class;
            default:
                Log.w(TAG, "未知或不支持的 SQLite 类型: " + sqliteType);
                return false;
        }
    }

    public static void addColumn(SQLiteDatabase db, String tableName, ColumnInfo newColumn) {
        StringBuilder alterTableSQL = new StringBuilder("ALTER TABLE ");
        alterTableSQL.append("`" + tableName + "`").append(" ADD COLUMN ");

        // 构建新的列定义
        alterTableSQL.append(newColumn.getColumnName()).append(" ").append(newColumn.getColumnType());

        if (newColumn.isNotNull()) {
            alterTableSQL.append(" NOT NULL");
            if (newColumn.getDefaultValueForSql() != null) {
                alterTableSQL.append(newColumn.getDefaultValueForSql());
            } else {
                throw new IllegalArgumentException("Cannot add a NOT NULL column without a default value to a non-empty table.");
            }
        } else if (newColumn.getDefaultValueForSql() != null) {
            alterTableSQL.append(newColumn.getDefaultValueForSql());
        }

        if (newColumn.isPrimaryKey()) {
            throw new UnsupportedOperationException("SQLite does not support adding a PRIMARY KEY column with ALTER TABLE.");
        }

        // 执行 SQL 语句
        db.execSQL(alterTableSQL.toString());
        Log.d(TAG, "表:"+tableName+",新增字段: "+newColumn.getColumnName());
    }

    /**
     * 判断该字段是否有其他约束
     *
     * @param con
     * @return
     */
    public static String getConstraints(ColumnInfo con) {
        StringBuilder constraints = new StringBuilder();
        if (con.isPrimaryKey()) {
            constraints.append(" PRIMARY KEY");
            if (con.isAutoIncrement()) {
                constraints.append(" AUTOINCREMENT");
            }
            constraints.append(" NOT NULL");  // 主键总是不允许为空
        } else {
            if (con.isNotNull()) {
                constraints.append(" NOT NULL");
            }
            if (con.isUnique()) {
                constraints.append(" UNIQUE");
            }
        }

        // 处理默认值，注意区分字符串和非字符串类型的默认值
        if (con.getDefaultValueForSql() != null) {
            String defaultValue = con.getDefaultValueForSql();
            Log.d(TAG, con.getColumnName()+",defaultValue:"+defaultValue);
            constraints.append(defaultValue);
        }
        return constraints.toString();
    }
    // 辅助方法：检查是否是字符串类型
    private static boolean isStringType(String columnType) {
        if (columnType == null) return false;
        String lowerCaseType = columnType.toLowerCase();
        return lowerCaseType.equals("text") || lowerCaseType.equals("varchar") || lowerCaseType.equals("char");
    }
    private static boolean isNumberType(String columnType) {
        if (columnType == null) return false;
        String lowerCaseType = columnType.toLowerCase();
        return lowerCaseType.equals("int") ||
                lowerCaseType.equals("integer") ||
                lowerCaseType.equals("tinyint") ||
                lowerCaseType.equals("smallint") ||
                lowerCaseType.equals("mediumint") ||
                lowerCaseType.equals("bigint") ||
                lowerCaseType.equals("float") ||
                lowerCaseType.equals("double") ||
                lowerCaseType.equals("real") ||
                lowerCaseType.equals("numeric") ||
                lowerCaseType.equals("decimal");
    }
    /**
     *   创建临时表：创建一个与原表结构相同的新表，但不包含需要移除的字段。
     *   迁移数据：将原表中的数据复制到新表中，忽略不需要的字段。
     *   删除旧表：删除原来的表。
     *   重命名新表：将新表重命名为原来表的名字。
     * @param db
     * @param tableName
     * @param columnName
     */
    public static void removeColumn(SQLiteDatabase db, String tableName, String columnName) {
        // 构建临时表名
        String tempTableName = tableName + "_temp";

        // 获取现有表的列信息
        List<ColumnInfo> existingColumns = getExistingColumns(db, tableName);
        List<String> columnsList = new ArrayList<>();

        for (ColumnInfo column : existingColumns) {
            if (!column.getColumnName().equals(columnName)) {
                columnsList.add(column.getColumnName());
            }
        }

        // 构建创建临时表的 SQL
        StringBuilder createTableSQL = new StringBuilder("CREATE TABLE ");
        createTableSQL.append(tempTableName).append(" AS SELECT ");
        createTableSQL.append(String.join(", ", columnsList)).append(" FROM ").append(tableName).append(";");

        db.beginTransaction();
        try {
            // 创建临时表
            db.execSQL(createTableSQL.toString());
            // 删除旧表
            db.execSQL("DROP TABLE " + tableName);
            // 重命名临时表为原来的表名
            db.execSQL("ALTER TABLE " + tempTableName + " RENAME TO " + tableName);
            db.setTransactionSuccessful();
            Log.d(TAG, "删除列成功："+columnName);
        } finally {
            db.endTransaction();
        }
    }

    public static void alterColumn(SQLiteDatabase db, String tableName, ColumnInfo oldColumn, ColumnInfo newColumn) {
        // 构建临时表名
        String tempTableName = tableName + "_temp";

        // 获取现有表的所有列信息
        List<ColumnInfo> existingColumns = getExistingColumns(db, tableName);

        // 构建创建临时表的 SQL
        StringBuilder createTableSQL = new StringBuilder("CREATE TABLE ");
        createTableSQL.append(tempTableName).append(" (");

        boolean isFirst = true;
        for (ColumnInfo column : existingColumns) {
            if (!isFirst) {
                createTableSQL.append(", ");
            }
            if (column.getColumnName().equals(oldColumn.getColumnName())) {
                createTableSQL.append(newColumn.getColumnName()).append(" ").append(newColumn.getColumnType());
                createTableSQL.append(getConstraints(newColumn));
            } else {
                createTableSQL.append(column.getColumnName()).append(" ").append(column.getColumnType());
                createTableSQL.append(getConstraints(column));
            }
            isFirst = false;
        }
        createTableSQL.append(");");

        // 执行操作
        db.beginTransaction();
        try {
            // 创建临时表
            db.execSQL(createTableSQL.toString());

            // 构建插入数据的 SQL
            StringBuilder insertSQL = new StringBuilder("INSERT INTO ").append(tempTableName).append(" (");
            StringBuilder selectSQL = new StringBuilder("SELECT ");

            isFirst = true;
            for (ColumnInfo column : existingColumns) {

                if (column.getColumnName().equals(oldColumn.getColumnName())) {
                    if((!newColumn.isUnique()||newColumn.isUnique()==column.isUnique())) {//唯一约束
                        if (!isFirst) {
                            insertSQL.append(", ");
                            selectSQL.append(", ");
                        }else {
                            isFirst = false;
                        }

                        insertSQL.append(newColumn.getColumnName());
                        String defaultValue = newColumn.getDefaultValueForSql();
                        if (TextUtils.isEmpty(defaultValue)) {
                            Log.e(TAG, "alterColumn: " + newColumn.getColumnName() + "字段没有默认值" + newColumn.getColumnType());
                            if (newColumn.getColumnType().equals("INTEGER")) {
                                defaultValue = "0";
                            } else {
                                defaultValue = "''";
                            }
                        }
                        selectSQL.append("COALESCE(").append(oldColumn.getColumnName()).append(",").append(defaultValue).append(")");
                    }
                } else {
                    if (!isFirst) {
                        insertSQL.append(", ");
                        selectSQL.append(", ");
                    }else {
                        isFirst = false;
                    }
                    insertSQL.append(column.getColumnName());
                    selectSQL.append(column.getColumnName());
                }
            }
            insertSQL.append(") ").append(selectSQL).append(" FROM ").append(tableName).append(";");

            Log.i(TAG, "alterColumn: " + insertSQL.toString());
            // 插入数据到临时表
            db.execSQL(insertSQL.toString());

            // 删除旧表
            db.execSQL("DROP TABLE " + tableName);

            // 重命名新表为原来的表名
            db.execSQL("ALTER TABLE " + tempTableName + " RENAME TO " + tableName);

            db.setTransactionSuccessful();
            Log.d(TAG, "修改列成功："+oldColumn.getColumnName()+"->"+newColumn.getColumnName());
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            db.endTransaction();
        }
    }

    /**
     * 获取指定表的创建 SQL 语句。
     *
     * @param database 数据库实例
     * @param tableName 表名
     * @return 创建表的 SQL 语句，如果表不存在则返回 null
     */
    public static String fetchTableCreationSql(SQLiteDatabase database, String tableName) {
        // 使用参数化查询防止 SQL 注入
        String query = "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?";

        // 使用 try-with-resources 确保 Cursor 在使用后自动关闭
        try (Cursor cursor = database.rawQuery(query, new String[]{tableName})) {
            if (cursor.moveToFirst()) {
                return cursor.getString(0);
            }
        } catch (Exception e) {
            // 记录异常信息，但不抛出，因为这可能只是表不存在的情况
            Log.e("DatabaseHelper", "Error fetching table creation SQL: " + e.getMessage());
        }

        // 如果没有找到对应的表或发生错误，则返回 null
        return null;
    }
}